/*
 * Copyright (c) 2011. This file is public under GPLv3 license.
 * For more information about license please refer to http://www.gnu.org/licenses/gpl.html
 */

package com.freetools.watchdog.engine;

import com.freetools.watchdog.model.IColumn;
import com.freetools.watchdog.model.IModelHolder;
import com.freetools.watchdog.model.IRecord;
import com.freetools.watchdog.model.Record;
import org.apache.log4j.Logger;

import java.io.IOException;
import java.io.Reader;
import java.io.StringReader;
import java.sql.*;
import java.util.AbstractCollection;
import java.util.Collection;
import java.util.Iterator;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Description to be do.
 *
 * @author Michael Morozov
 */
public class DAO
{

    private static final Logger log = Logger.getLogger(DAO.class);
    private static final String JDBC_DRIVER_NAME = "org.h2.Driver";
    private static String JDBC_URL = "jdbc:h2:file:%s";
    public static final String DEFAULT_DB_FILE = "watch_dog";
    private static final String JDBC_USER = "";
    private static final String JDBC_PASSWORD = "";
    private static Connection connection;
    private static final String CREATE_DB_SQL =
            "drop table if exists sources;\n" +
                    "drop table if exists src_recs;\n" +
                    "drop table if exists records;\n" +
                    "create table sources(\n" +
                    "  id identity primary key,  \n" +
                    "  name varchar(100),\n" +
                    "  last_modified timestamp);\n" +
                    "alter table sources add constraint \"u_source_name\" unique (name, last_modified);\n" +
                    "create table records (\n" +
                    "  id identity primary key);\n" +
                    "create table src_recs(\n" +
                    "  source_id bigint, \n" +
                    "  rec_id bigint);\n" +
                    "alter table src_recs add constraint fk_source foreign key (source_id) references sources (id) on delete cascade;\n" +
                    "alter table src_recs add constraint fk_rec foreign key (rec_id) references records (id) on delete cascade;\n";
    private static final String ALTER_RECORDS_SQL = "alter table records add %s %s;";
    private static final String CREATE_INDEX_SQL = "create index if not exists ix_%1$s on records (%1$s);";
    public static final String DEFAULT_QUERY = "select * from records where 1=1";
    private static String insertQuery;

    private static class RecordIterator implements Iterator<IRecord>
    {
        private ResultSet resultSet;
        private IModelHolder model;
        private boolean hasNext;

        private RecordIterator(ResultSet resultSet, IModelHolder model)
        {
            this.resultSet = resultSet;
            this.model = model;
            try
            {
                hasNext = !resultSet.isAfterLast();
            } catch (SQLException e)
            {
                log.error(e);
                hasNext = false;
            }
        }

        public boolean hasNext()
        {
            try
            {
                return !resultSet.isLast();
            } catch (SQLException e)
            {
                log.error(e);
                return false;
            }
        }

        public IRecord next()
        {
            IRecord record = null;
            try
            {
                hasNext = resultSet.next();
                long id = resultSet.getLong("ID");
                record = new Record(id);
                for (IColumn column : model.getColumns())
                {
                    if ("timestamp".equalsIgnoreCase(column.getType()))
                    {
                        record.setValue(resultSet.getDate(column.getName()), column);
                    } else if ("bigint".equalsIgnoreCase(column.getType()))
                    {
                        record.setValue(resultSet.getLong(column.getName()), column);
                    } else if ("clob".equalsIgnoreCase(column.getType()))
                    {
                        Clob clob = resultSet.getClob(column.getName());
                        char[] buffer = new char[(int) clob.length()];
                        int read = -1;
                        Reader reader = clob.getCharacterStream();
                        StringBuilder stringBuilder = new StringBuilder();
                        while ((read = reader.read(buffer)) > 0)
                        {
                            stringBuilder.append(buffer, 0, read - 1);
                        }
                        reader.close();
                        record.setValue(stringBuilder.toString(), column);
                    } else
                    {
                        record.setValue(resultSet.getString(column.getName()), column);
                    }
                }
            } catch (SQLException e)
            {
                DAO.log.error(e);
            } catch (IOException e)
            {
                DAO.log.error(e);
            }
            return record;
        }

        public void remove()
        {
        }
    }

    private static class RecordsCollection extends AbstractCollection<IRecord>
    {

        private ResultSet resultSet;
        private IModelHolder model;
        private int count;

        private RecordsCollection(ResultSet resultSet, IModelHolder model, int count)
        {
            this.resultSet = resultSet;
            this.model = model;
            this.count = count;
        }

        @Override
        public Iterator<IRecord> iterator()
        {
            return new RecordIterator(resultSet, model);
        }

        @Override
        public int size()
        {
            return count;
        }
    }

    private static Connection getConnection(IModelHolder model) throws SQLException
    {
        if (connection == null)
        {
            try
            {
                Class.forName(JDBC_DRIVER_NAME);
            } catch (ClassNotFoundException ignore)
            {
            }
            connection = DriverManager.getConnection(String.format(JDBC_URL, model.getDBFile()), JDBC_USER, JDBC_PASSWORD);
            //todo: make this more graceful to know create or not db tables
            if (connection != null)
            {
                try
                {
                    connection.createStatement().executeQuery("select 1 from records where 1=0");
                } catch (SQLException e)
                {
                    createDB(model);
                }
            }
        }
        return connection;
    }

    public static void storeRecord(IModelHolder model, IRecord record)
    {
        if (insertQuery == null)
        {
            insertQuery = getInsertQuery(model);
        }
        try
        {
            PreparedStatement preparedStatement = getConnection(model).prepareStatement(insertQuery);
            int i = 0;
            for (IColumn column : model.getColumns())
            {
                i++;
                if ("timestamp".equalsIgnoreCase(column.getType()))
                {
                    preparedStatement.setTimestamp(i, (Timestamp) record.getValue(column));
                } else if ("bigint".equalsIgnoreCase(column.getType()))
                {
                    preparedStatement.setLong(i, (Long) record.getValue(column));
                } else if ("clob".equalsIgnoreCase(column.getType()))
                {
                    String recordValue = (String) record.getValue(column);
                    StringReader reader = new StringReader(recordValue);
                    preparedStatement.setCharacterStream(i, reader, recordValue.length());
                } else
                {
                    preparedStatement.setString(i, (String) record.getValue(column));
                }

            }
            preparedStatement.executeUpdate();
            preparedStatement.close();
        } catch (SQLException e)
        {
            log.error("Could not store record", e);
        }
    }

    private static String getInsertQuery(IModelHolder model)
    {
        StringBuilder query = new StringBuilder("insert into records (");
        StringBuilder queryValues = new StringBuilder(") values (");
        for (IColumn column : model.getColumns())
        {
            query.append(column.getName()).append(",");
            queryValues.append("?").append(",");
        }
        query.delete(query.length() - 1, query.length())
                .append(queryValues.delete(queryValues.length() - 1, queryValues.length()))
                .append(")");
        return query.toString();
    }

    public static Collection<IRecord> getRecords(IModelHolder model)
    {
        String query = model.getQuery();
        try
        {
            int count = 0;
            Pattern countPattern = Pattern.compile("select (.*?)\\s?from.*?(order by .*?\\z)", Pattern.CASE_INSENSITIVE | Pattern.DOTALL | Pattern.MULTILINE);
            StringBuilder countQuery = new StringBuilder(query);
            Matcher matcher1 = countPattern.matcher(countQuery);
            if (matcher1.find())
            {
                int start = matcher1.start(1);
                countQuery
                        .delete(matcher1.start(2), matcher1.end(2))
                        .delete(matcher1.start(1), matcher1.end(1))
                        .insert(matcher1.start(1), "count(1) ");
                ResultSet countResultSet = getConnection(model).createStatement().executeQuery(countQuery.toString());
                countResultSet.next();
                count = countResultSet.getInt(1);

            }
            return new RecordsCollection(getConnection(model).createStatement().executeQuery(query), model, count);
        } catch (SQLException e)
        {
            log.error("Could not get any record because of SQL exception", e);
            throw new RuntimeException(e);
        }
    }

    public static void createDB(IModelHolder model)
    {
        UserInformation.info("Creating database...");
        insertQuery = null;
        try
        {
            Statement st = getConnection(model).createStatement();
            st.execute(CREATE_DB_SQL);
            for (IColumn column : model.getColumns())
            {
                st.execute(String.format(ALTER_RECORDS_SQL, column.getName(), column.getType()));
                if (column.hasIndex())
                {
                    st.execute(String.format(CREATE_INDEX_SQL, column.getName()));
                }
            }
        } catch (SQLException e)
        {
            log.error("Error when creating database", e);
            UserInformation.info("Error when creating database");
        }
        UserInformation.info("Database has been created.");
    }
}
